---------------------------- 게시판 분류 테이블 ---------------------------- 
CREATE TABLE board_class(
	class_id NUMBER,
	class_name VARCHAR2(100) NOT NULL,
	CONSTRAINT board_class_pk PRIMARY KEY(class_id)
);

--분류ID 지정을 위한 시쿼스 생성
CREATE SEQUENCE board_class_seq
	MINVALUE 1
    INCREMENT BY 1
    START WITH 1
    NOCACHE;
    
INSERT INTO board_class VALUES(board_class_seq.NEXTVAL, '운영');
INSERT INTO board_class VALUES(board_class_seq.NEXTVAL, '일반');
INSERT INTO board_class VALUES(board_class_seq.NEXTVAL, '상품');
INSERT INTO board_class VALUES(board_class_seq.NEXTVAL, '자료실');
INSERT INTO board_class VALUES(board_class_seq.NEXTVAL, '기타');

SELECT * FROM board_class;
SELECT class_id, class_name FROM board_class;
SELECT class_name FROM board_class WHERE class_id=1;

DROP TABLE board_class;
DROP SEQUENCE board_class_seq;


---------------------------- 게시판 관리 테이블 ---------------------------- 
--공지사항 카테고리 생성
CREATE TABLE board_management(
	board_id NUMBER,
	class_id NUMBER DEFAULT 1 NOT NULL,
	board_title VARCHAR2(150) DEFAULT '제목없음' NOT NULL,
	open NUMBER DEFAULT 1 NOT NULL,
	paging_list_num NUMBER DEFAULT 15 NOT NULL,
	paging_num NUMBER DEFAULT 10 NOT NULL,
	partition NUMBER DEFAULT 1 NOT NULL,
	file_add NUMBER DEFAULT 0 NOT NULL,
	answer NUMBER DEFAULT 0 NOT NULL,
	sort NUMBER DEFAULT 1 NOT NULL,
	reply NUMBER DEFAULT 0 NOT NULL,
	reply2 NUMBER DEFAULT 0 NOT NULL,
	secret NUMBER DEFAULT 0 NOT NULL,
	secret_all NUMBER DEFAULT 0 NOT NULL,
	secret_reply NUMBER DEFAULT 0 NOT NULL,
	writer NUMBER DEFAULT 1 NOT NULL,
	write_date NUMBER DEFAULT 1 NOT NULL,
	cnt NUMBER DEFAULT 1 NOT NULL,
	write_btn NUMBER DEFAULT 0 NOT NULL,
	email NUMBER DEFAULT 0 NOT NULL,
	new_time NUMBER DEFAULT 24 NOT NULL,
	image NUMBER DEFAULT 0 NOT NULL,
	w_permission NUMBER DEFAULT 1 NOT NULL,
	r_permission NUMBER DEFAULT 3 NOT NULL,
	a_permission NUMBER DEFAULT 1 NOT NULL,
	rp_permission NUMBER DEFAULT 2 NOT NULL,
	paper_delete NUMBER DEFAULT 1 NOT NULL,
	d_option NUMBER DEFAULT 1 NOT NULL,
	rp_paging NUMBER DEFAULT 0 NOT NULL,
	rp_paging_list_num NUMBER DEFAULT 10 NOT NULL,
	rp_paging_num NUMBER DEFAULT 10 NOT NULL,
	rp_sort NUMBER DEFAULT 1 NOT NULL,
	title_limit NUMBER DEFAULT 0 NOT NULL,
	link NUMBER DEFAULT 1 NOT NULL,
	html NUMBER DEFAULT 0 NOT NULL,
	r_click NUMBER DEFAULT 1 NOT NULL,
	CONSTRAINT board_management_pk PRIMARY KEY(board_id),
	CONSTRAINT board_management_fk FOREIGN KEY(class_id) REFERENCES board_class(class_id)
);
	    
INSERT INTO board_management(board_id) VALUES(5);
--일반게시판 카테고리 생성
--상품 테이블 카테고리 생성

--테이블명 지정을 위한 시쿼스 생성
CREATE SEQUENCE board_management_seq
	MINVALUE 1
    INCREMENT BY 1
    START WITH 1
    NOCACHE;

insert into board_management(board_id) values(
	board_management_seq.nextval
);
insert into board_management(board_id, class_id) values(
	board_management_seq.nextval, 5
);

DELETE FROM board_management;
DELETE FROM board_management WHERE board_id=11;

SELECT * FROM board_management ORDER BY board_id ASC;
SELECT * FROM board_1_paper;
SELECT board_management_seq.NEXTVAL FROM DUAL;   

SELECT board_id boardId, class_id classId, board_title boardTitle, open, paging_list_num pagingListNum, paging_num pagingNum, 
 				partition, file_add fileAdd, answer, sort, reply, reply2, secret, secret_all secretAll, secret_reply secretReply, 
 				writer, write_date writeDate, cnt, write_btn writeBtn, email, new_time newTime, image, w_permission wPermission, 
 				r_permission rPermission, a_permission aPermission, rp_permission rpPermission, paper_delete paperDelete, d_option dOption, 
 				rp_paging rpPaging, rp_paging_list_num rpPagingListNum, rp_paging_num rpPagingNum, rp_sort rpSort, title_limit titleLimit, 
 				link, html, r_click rClick 
	 	FROM board_management WHERE board_id=1;

	 	
UPDATE board_management 
	SET class_id=#{classId}, board_title=#{boardTitle}, open=#{open}, paging_list_num=#{pagingListNum}, paging_num=#{pagingNum}, partition=#{partition}, 
		file_add=#{fileAdd}, answer=#{answer}, sort=#{sort}, reply=#{reply}, reply2=#{reply2}, secret=#{secret}, secret_all=#{secretAll}, 
		secret_reply=#{secretReply}, writer=#{writer}, write_date=#{writeDate}, cnt=#{cnt}, write_btn=#{writeBtn}, email=#{email}, new_time=#{newTime}, 
		image=#{image}, w_permission=#{wPermission}, r_permission=#{rPermission}, a_permission=#{aPermission}, rp_permission=#{rpPermission}, 
		paper_delete=#{paperDelete}, d_option=#{dOption}, rp_paging=#{rpPaging}, rp_paging_list_num=#{rpPagingListNum}, rp_paging_num=#{rpPagingNum}, 
		rp_sort=#{rpSort}, title_limit=#{titleLimit}, link=#{link}, html=#{html}, r_click=#{rClick}
	WHERE board_id=#{boardId};

DROP TABLE board_management;
DROP SEQUENCE board_management_seq;



---------------------------- 게시판 순서 테이블  ---------------------------- 
drop table board_sort;

CREATE TABLE board_sort(
	id NUMBER,
	no NUMBER NOT NULL,
	CONSTRAINT board_sort_fk FOREIGN KEY(id) REFERENCES board_management(board_id) ON DELETE CASCADE
);



select * from board_sort order by no asc;
select bs.no, bm.board_id, bm.board_title from board_sort bs, board_management bm where bs.ID = bm.board_id order by bs.no asc;
select bc.CLASS_NAME boardClass, bm.BOARD_TITLE boardTitle, bm.BOARD_ID boardId, bm.W_PERMISSION wPermission, bm.R_PERMISSION rPermission, bm."OPEN" open from board_management bm, board_class bc, board_sort bs where bm.CLASS_ID = bc.CLASS_ID and bm.board_id = bs.id ORDER BY bs.no ASC;
delete from board_sort;
insert into board_sort(no, id) values(1, 30);
insert into board_sort(no, id) values(2, 29);
insert into board_sort(no, id) values(3, 31);

-- board_management 행 삭제시 board_sort 자동 정렬을 위한 트리거 생성
drop trigger delete_board;
	
  CREATE OR REPLACE TRIGGER delete_board
	AFTER DELETE ON board_management
	for each row
	DECLARE
		-- 자율 트랜잭션
		-- 부모 트랜잭션의 승인/롤백 여부에 상관없이 자신의 작업을 승인하거나 롤백할 수 있다.
		pragma autonomous_transaction;
		max_no number;
		del_no number;
	BEGIN
		if deleting then
			dbms_output.put_line('deleting');
			select max(no) into max_no from board_sort;
			select no into del_no from board_sort where id=:OLD.board_id;
			if max_no > del_no then
				update board_sort set no=no-1 where no > del_no;
				commit; --commit 구문 빠졌을시 trigger 오류 발생
			end if;
		end if;
	END;
	
-- board_management 행 추가시 board_sort 가장 마지막 no로 자동 등록을 수행하는 트리거 생성	
drop trigger insert_board;

  CREATE OR REPLACE TRIGGER insert_board
	AFTER INSERT ON board_management
	for each row
	BEGIN
		if inserting then
			dbms_output.put_line('inserting');
			insert into board_sort(no, id) values((select NVL(max(no+1), 1) from board_sort), :NEW.board_id);
		end if;
	END;

--board_sort 임의 정렬
update board_sort set no=1 where ID='30';
update board_sort set no=2 where ID='29';
select * from board_management;
select * from board_sort;
  
--delete_board 트리거 테스트--
delete from board_management where board_id=29;
--delete from board_management where board_id in (select id from product where CATEGORY_NAME='CPU');
select * from board_sort order by no asc;
select bs.NO, bs.ID, bm.BOARD_TITLE from board_management bm, board_sort bs where bs.ID = bm.BOARD_ID order by no asc;


---------------------------- 게시판내 분류 테이블 ---------------------------- 
CREATE TABLE board_partition(
	partition_id NUMBER,
	partition_name VARCHAR2(100) NOT NULL,
	board_id NUMBER NOT NULL,
	CONSTRAINT board_partition_pk PRIMARY KEY(partition_id),
	CONSTRAINT board_partition_fk FOREIGN KEY(board_id) REFERENCES board_management(board_id) ON DELETE CASCADE
);

--게시판내 분류ID 지정을 위한 시쿼스 생성
CREATE SEQUENCE board_partition_seq
	MINVALUE 1
    INCREMENT BY 1
    START WITH 1
    NOCACHE;
    
INSERT INTO board_partition VALUES(board_partition_seq.NEXTVAL, '자두', 10);
INSERT INTO board_partition VALUES(board_partition_seq.NEXTVAL, '잉꼬', 7);

SELECT * FROM board_partition;

DROP TABLE board_partition;
DROP SEQUENCE board_partition_seq;



---------------------------- 게시물 테이블 ---------------------------- 
--이건 사용하면 안됨, mybatis로 동적 생성함
CREATE TABLE board_paper(
	paper_num NUMBER,
	partition_id NUMBER,
	title VARCHAR2(100 CHAR) NOT NULL,
	notice NUMBER NOT NULL,
	writer VARCHAR2(50),
	pc_id VARCHAR2(20),
	content VARCHAR2(4000) NOT NULL,
	file_add VARCHAR2(1000),
	image VARCHAR2(1000),
	refamily NUMBER NOT NULL,	-- 원본글을 기준으로 그 답변글들을 묶는 값
	restep number NOT NULL,		-- refamily가 같은 글들간의 정렬 순서를 정의하는 값
	relevel number NOT NULL,	-- 답변 레벨 (원본 : 0 이후는 +1씩)
	secret NUMBER NOT NULL,
	password VARCHAR2(50),
	write_date DATE NOT NULL,
	cnt NUMBER NOT NULL,
	email VARCHAR2(50),
	answer NUMBER,
	del NUMBER NOT NULL,
	d_date DATE,
	manager_id VARCHAR2(15),
	CONSTRAINT board_paper_pk PRIMARY KEY(paper_num),
	CONSTRAINT board_paper_pc_fk FOREIGN KEY(pc_id) REFERENCES complete_pc(complete_id) ON DELETE SET NULL
	--CONSTRAINT board_paper_parent_fk FOREIGN KEY(parent_num) REFERENCES board_paper(paper_num) ON DELETE SET NULL
);

CREATE SEQUENCE board_paper_seq
	MINVALUE 1
    INCREMENT BY 1
    START WITH 1
    NOCACHE;

SELECT * FROM board_paper;

DROP TABLE board_paper;
DROP SEQUENCE board_paper_seq;

select * from board_management ;
select * from board_56_paper;
DROP TABLE board_56_paper;
DROP SEQUENCE board_18_paper_seq;






SELECT paper_num paperNum, partition_id partitionId, title, notice, 
				writer, pc_id pcId, content, file_add fileAdd, image, 
				relevel, secret, password, 
				--to_char(to_date(write_date, 'yyyymmddhh24miss'), 'yyyy-mm-dd') writeDate, 
				write_date writeDate, 
				cnt, email, answer, del, 
				--to_char(to_date(d_date, 'yyyymmddhh24miss'), 'yyyy-mm-dd') dDate, 
				d_date dDate, 
				manager_id managerId
			FROM(SELECT	CEIL(ROWNUM/10) page, paper_num, partition_id, 
						title, notice, writer, pc_id, content, file_add, image, relevel, 
						secret, password, write_date, cnt, email, answer, del, d_date, manager_id 
					FROM(SELECT	paper_num, partition_id, title, notice, writer, pc_id, 
								content, file_add, image, relevel, secret, password, write_date, 
								cnt, email, answer, del, d_date, manager_id
							FROM BOARD_20_PAPER order by refamily desc, restep asc
					)
			)
			WHERE page=1;
			
select * from BOARD_6_PAPER;
create table BOARD_6_PAPER(a number);
drop table BOARD_6_PAPER;

---------------------------- 댓글 테이블 ---------------------------- 
--이건 사용하면 안됨, mybatis로 동적 생성함
CREATE TABLE board_reply(
	rp_num NUMBER,
	paper_num NUMBER NOT NULL,
	writer VARCHAR2(50),
	content VARCHAR2(1000) NOT NULL,
	parent_num NUMBER,
	secret NUMBER NOT NULL,
	write_date DATE NOT NULL,
	CONSTRAINT board_rp_pk PRIMARY KEY(rp_num),
	CONSTRAINT board_rp_paper_fk FOREIGN KEY(paper_num) REFERENCES board_paper(paper_num) ON DELETE CASCADE,
	CONSTRAINT board_rp_cust_fk FOREIGN KEY(cust_id) REFERENCES customer(cust_id) ON DELETE SET NULL,
	CONSTRAINT board_rp_parent_fk FOREIGN KEY(parent_num) REFERENCES board_reply(rp_num) ON DELETE SET NULL
);

SELECT * FROM board_reply;

DROP TABLE board_reply;


---------------------------------------------------------------------- 
------------------------------- select 조합 -------------------------------
---------------------------------------------------------------------- 
-- boardList 구현
select bc.CLASS_NAME boardClass, bm.BOARD_TITLE boardTitle, bm.BOARD_ID boardId, bm.W_PERMISSION wPermission, bm.R_PERMISSION rPermission, bm."OPEN" open from board_management bm, board_class bc where bm.CLASS_ID = bc.CLASS_ID ORDER BY board_id ASC;





---------------------------------------------------------------------- 
------------------------------- 임시 테이블 -------------------------------
---------------------------------------------------------------------- 
--체크박스 / 분류 / 게시판제목 / 게시판ID / 권한 / 글개수 / 관리 / 공개여부
--select bm.class_id, bm.board_title, bm.board_id, bm.w_permission, bm.r_permission, 
select * from board_management;
insert into board_management(board_id, class_id) values(57, 2);
insert into board_management(board_id, class_id) values(58, 2);
insert into board_management(board_id, class_id) values(59, 1);
insert into board_management(board_id, class_id) values(60, 4);

select class_id from board_management;
select bc.CLASS_NAME, bm.BOARD_TITLE, bm.BOARD_ID, bm.W_PERMISSION, bm.R_PERMISSION, bm."OPEN" from board_management bm, board_class bc where bm.CLASS_ID = bc.CLASS_ID ORDER BY board_id ASC;
--(SELECT COUNT(*) FROM Orders o WHERE o.CustId=c.Id) COUNT
---------------------------- 회원 테이블 ---------------------------- 
create table customer(
		cust_id varchar2(50),
		password varchar2(50) not null,
		name varchar2(50) not null,
		birth date not null,
		email varchar2(50) not null,
		phone varchar2(30) not null,
		address varchar2(30) not null,
		join_date date not null,
		constraint customer_pk primary key (cust_id)
);

insert into customer (cust_id, password, name, birth, email, phone, address, join_date) 
values ('1','111','임정원','88.10.10','a@naver.com','010','wndi','2014/11/11');

delete from customer;

select * from customer;
select cust_id, password, name, birth, email, phone, address, join_date joinDate
  	from customer where cust_id='1';

drop table customer;



---------------------------- 완제품PC 테이블 ---------------------------- 
create table complete_pc(
	complete_id		varchar2(20) primary key,
	set_id  varchar2(20) not null
);

drop table complete_pc;


---------------------------- 운영자 테이블 ---------------------------- 
DROP TABLE SHOP_MANAGER;

CREATE TABLE SHOP_MANAGER(
	SHOP_MANAGER_ID		VARCHAR2(15)		PRIMARY KEY,
	NAME									VARCHAR2(30)		NOT NULL,
	TEL											VARCHAR2(15)		NOT NULL,
	EMAIL									VARCHAR2(20)		NOT NULL,
	SORT										VARCHAR2(15)		NOT NULL,
	PWD										VARCHAR2(15)		NOT NULL,
	PAGE_ACCESS_RIGHT		VARCHAR2(50)		NOT NULL
);